﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace Test.MySql.JsonTests.Dictionary
{
    [TestFixture]
    internal class IDictionaryTests : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }
            [JsonStore(Bucket = "p_dic")]
            public IDictionary<string, string[]> p_dic { get; set; }
            [JsonStore(Bucket = "p_dic2")]
            public IDictionary<string, List<int>> p_dic2 { get; set; }
            [JsonStore(Bucket = "p_dic3")]
            public IDictionary<string, object> p_dic3 { get; set; }
        }

        [Table("test2")]
        public class PersonKey
        {
            [PrimaryKey(KeyStrategy = KeyStrategy.Identity)]
            [Column("id")]
            public int Id { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_dic")]
            public IDictionary<string, string[]> p_dic { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_dic2")]
            public IDictionary<string, List<int>> p_dic2 { get; set; }
            [JsonStore(Bucket = "ext", Key = "p_dic3")]
            public IDictionary<string, object> p_dic3 { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("""
                create table test(id int primary key auto_increment,p_dic json,p_dic2 json,p_dic3 json)
                """);
            db.ExecuteSql("""
                insert into test(p_dic,p_dic2,p_dic3) values
                    ('{"names":["jack","tom"]}','{"ages":[18,19]}','{"name":"jack","age":18}'),
                    ('{"names":["lisa","wang"]}','{"ages":[20,19]}','{"name":"lisa","age":20}');
                """);

            DropTableIfExist("test2");
            db.ExecuteSql("""
                create table test2(id int primary key auto_increment,ext json)
                """);
            db.ExecuteSql("""
                insert into test2(ext) values
                    ('{"p_dic":{"names":["jack","tom"]},"p_dic2":{"ages":[18,19]},"p_dic3":{"name":"jack","age":18}}'),
                    ('{"p_dic":{"names":["lisa","wang"]},"p_dic2":{"ages":[20,19]},"p_dic3":{"name":"lisa","age":20}}');
                """);
        }

        #region NoKey
        [Test]
        public void TestInsert()
        {
            var insert = db.Insert<Person>().SetEntity(new Person
            {
                p_dic = new Dictionary<string, string[]>().SetFluent("names", ["jack", "tom"]),
                p_dic2 = new Dictionary<string, List<int>>().SetFluent("ages", [18, 19]),
                p_dic3 = new Dictionary<string, object>().SetFluent("name", "lisa").SetFluent("age", 20),
            });
            var sql = insert.ToSql();
            //sql.ShouldBe("""
            //    insert into test(p_dic,p_dic2,p_dic3) values('{"names":["jack","tom"]}','{"ages":[18,19]}','{"name":"lisa","age":20}');
            //    """);
            var p = insert.ExecuteInserted();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":3,"p_dic":{"names":["jack","tom"]},"p_dic2":{"ages":[18,19]},"p_dic3":{"age":20,"name":"lisa"}}""");
        }

        [Test]
        public void TestSelect()
        {
            var select = db.Select<Person>().Where(i => i.p_dic.Count > 0);
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.p_dic,'$') `p_dic`,json_value(t.p_dic2,'$') `p_dic2`,json_value(t.p_dic3,'$') `p_dic3`
            //    from test t
            //    where json_length(json_value(t.p_dic,'$')) > 0;
            //    """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_dic":{"names":["jack","tom"]},"p_dic2":{"ages":[18,19]},"p_dic3":{"age":18,"name":"jack"}},{"Id":2,"p_dic":{"names":["lisa","wang"]},"p_dic2":{"ages":[20,19]},"p_dic3":{"age":20,"name":"lisa"}}]""");
        }
        #endregion

        #region Key
        [Test]
        public void TestInsertKey()
        {
            var insert = db.Insert<PersonKey>().SetEntity(new PersonKey
            {
                p_dic = new Dictionary<string, string[]>().SetFluent("names", ["jack", "tom"]),
                p_dic2 = new Dictionary<string, List<int>>().SetFluent("ages", [18, 19]),
                p_dic3 = new Dictionary<string, object>().SetFluent("name", "lisa").SetFluent("age", 20),
            });
            var sql = insert.ToSql();
            //sql.ShouldBe("""
            //    insert into test2(ext) values(json_object('p_dic',cast('{"names":["jack","tom"]}' as json),'p_dic2',cast('{"ages":[18,19]}' as json),'p_dic3',cast('{"name":"lisa","age":20}' as json)));
            //    """);
            var p = insert.ExecuteInserted();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":3,"p_dic":{"names":["jack","tom"]},"p_dic2":{"ages":[18,19]},"p_dic3":{"age":20,"name":"lisa"}}""");
        }

        [Test]
        public void TestSelectKey()
        {
            var select = db.Select<PersonKey>().Where(i => i.p_dic.Count > 0);
            var sql = select.ToSqlList();
            //sql.ShouldBe("""
            //    select t.id `Id`,json_value(t.ext,'$."p_dic"') `p_dic`,json_value(t.ext,'$."p_dic2"') `p_dic2`,json_value(t.ext,'$."p_dic3"') `p_dic3`
            //    from test2 t
            //    where json_length(json_value(t.ext,'$."p_dic"')) > 0;
            //    """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_dic":{"names":["jack","tom"]},"p_dic2":{"ages":[18,19]},"p_dic3":{"age":18,"name":"jack"}},{"Id":2,"p_dic":{"names":["lisa","wang"]},"p_dic2":{"ages":[20,19]},"p_dic3":{"age":20,"name":"lisa"}}]""");
        }
        #endregion
    }
}
